複習: 在昨天講解了如何使用mysqldump備份是數據和binlog相關過濾方法,今天來實際模擬一下流程進行。
資料復原準備
使用binlog復原資料的流程,假設我們今天因為異動操作誤刪了表中資料要如何復原資料?
DB:user
TABLE:user_powers
--DATA SQL資料參考--
(資料庫)
CREATE DATABASE user;
----------------------------------------------------
(資料表)
CREATE TABLE `user_powers` (
`user_id` varchar(50) NOT NULL COMMENT '會員ID',
`nick_name` varchar(20) NOT NULL COMMENT '會員名稱',
`group_id` int(11) DEFAULT NULL COMMENT '會員所屬平台',
`updated_at` timestamp NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP COMMENT '異動時間',
PRIMARY KEY (`user_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8
----------------------------------------------------
(資料)
INSERT INTO `user_powers` VALUES ('1021211','ken',119201,'2021-08-10 08:33:56'),('1232123','siang',19200,'2021-08-10 08:33:51'),('18675543','kitty',19200,'2021-08-10 08:33:51'),('23122141','kiki',1242000,'2021-08-12 09:01:54'),('24122441','snow',119201,'2021-08-10 08:33:51'),('4356231','lisa',19200,'2021-08-10 08:33:56'),('64121211','kevin',389110,'2021-08-10 08:33:51'),('7622441','ben',213123,'2021-08-10 08:33:56'),('91311233','alex',389110,'2021-08-10 08:33:56'),('91675543','kay',123321,'2021-08-10 08:33:56'),('98212321','sara',119201,'2021-08-10 08:33:51');
----------------------------------------------------
(完成後數據)
mysql> select * from user_powers;
+----------+-----------+----------+---------------------+
| user_id | nick_name | group_id | updated_at |
+----------+-----------+----------+---------------------+
| 1021211 | ken | 119201 | 2021-08-10 08:33:56 |
| 1232123 | siang | 19200 | 2021-08-10 08:33:51 |
| 18675543 | kitty | 19200 | 2021-08-10 08:33:51 |
| 23122141 | kiki | 1242000 | 2021-08-12 09:01:54 |
| 24122441 | snow | 119201 | 2021-08-10 08:33:51 |
| 4356231 | lisa | 19200 | 2021-08-10 08:33:56 |
| 64121211 | kevin | 389110 | 2021-08-10 08:33:51 |
| 7622441 | ben | 213123 | 2021-08-10 08:33:56 |
| 91311233 | alex | 389110 | 2021-08-10 08:33:56 |
| 91675543 | kay | 123321 | 2021-08-10 08:33:56 |
| 98212321 | sara | 119201 | 2021-08-10 08:33:51 |
+----------+-----------+----------+---------------------+
11 rows in set (0.00 sec)
1.假設上面數據為今天備份完的內容
重要*3
在下面的指令中加上 --master-data=2 參數作用會紀錄此備份內容是到哪一個binlog(POS點&檔名),方便後續復原動作。
# mysqldump -uroot -p1234 --routines --events --single-transaction --master-data=2 --flush-logs user > user_backup_20210817.sql
# grep -i "CHANGE MASTER TO MASTER_LOG_FILE" user_backup_20210817.sql
mysql> show master logs;
+------------------+-----------+
| Log_name | File_size |
+------------------+-----------+
| mysql-bin.000011 | 201 |
| mysql-bin.000012 | 3068 |
| mysql-bin.000013 | 154 | <-目前位置
+------------------+-----------+
3 rows in set (0.00 sec)
--補充--
環境備份:基本上生產環境下備份都是以全庫備為主,而有些重要的表資訊可能會有備份單庫or表的需求。
mysqldump還原注意: 備份時如果不是用指定到表的方式備份 ex.mysqldump -u root -p db_name tbl_name ... 的話在恢復時是沒辦法直接以恢復單一TABLE的方式還原資料倒回去的,可採用過濾備份資料把表相關資料語法另外撈出來做復原。
保險操作: 將備份檔和binlog資料在臨時庫上做復原動作,根據備份檔紀錄的binlog(POS&檔名)找後續執行的binlog內容進行復原,確認資料沒問題後再將表倒回生產庫中。
避免出錯造成二次傷害的狀況!!!
2.由於正常服務是持續運行的,備份完當然會有資料持續的更新寫入。
mysql> INSERT INTO `user_powers` VALUES ('98112111','leo',19201,'2021-08-13 12:33:56'),('38921112','shawn',19200,'2021-08-12 10:33:51');
mysql> INSERT INTO `user_powers` VALUES ('72197123','eric',19200,'2021-08-15 01:33:51');
mysql> FLUSH LOGS;
mysql> UPDATE user_powers SET nick_name = 'siangx' WHERE user_id = 1232123;
mysql> select * from user_powers;
+----------+-----------+----------+---------------------+
| user_id | nick_name | group_id | updated_at |
+----------+-----------+----------+---------------------+
| 1021211 | ken | 119201 | 2021-08-10 08:33:56 |
| 1232123 | siangx | 19200 | 2021-08-17 02:08:05 |
| 18675543 | kitty | 19200 | 2021-08-10 08:33:51 |
| 23122141 | kiki | 1242000 | 2021-08-12 09:01:54 |
| 24122441 | snow | 119201 | 2021-08-10 08:33:51 |
| 38921112 | shawn | 19200 | 2021-08-12 10:33:51 |
| 4356231 | lisa | 19200 | 2021-08-10 08:33:56 |
| 64121211 | kevin | 389110 | 2021-08-10 08:33:51 |
| 72197123 | eric | 19200 | 2021-08-15 01:33:51 |
| 7622441 | ben | 213123 | 2021-08-10 08:33:56 |
| 91311233 | alex | 389110 | 2021-08-10 08:33:56 |
| 91675543 | kay | 123321 | 2021-08-10 08:33:56 |
| 98112111 | leo | 19201 | 2021-08-13 12:33:56 |
| 98212321 | sara | 119201 | 2021-08-10 08:33:51 |
+----------+-----------+----------+---------------------+
14 rows in set (0.00 sec)
3.執行誤刪數據動作
mysql> delete from user_powers where group_id = 19200;
mysql> select * from user_powers;
+----------+-----------+----------+---------------------+
| user_id | nick_name | group_id | updated_at |
+----------+-----------+----------+---------------------+
| 1021211 | ken | 119201 | 2021-08-10 08:33:56 |
| 23122141 | kiki | 1242000 | 2021-08-12 09:01:54 |
| 24122441 | snow | 119201 | 2021-08-10 08:33:51 |
| 64121211 | kevin | 389110 | 2021-08-10 08:33:51 |
| 7622441 | ben | 213123 | 2021-08-10 08:33:56 |
| 91311233 | alex | 389110 | 2021-08-10 08:33:56 |
| 91675543 | kay | 123321 | 2021-08-10 08:33:56 |
| 98112111 | leo | 19201 | 2021-08-13 12:33:56 |
| 98212321 | sara | 119201 | 2021-08-10 08:33:51 |
+----------+-----------+----------+---------------------+
9 rows in set (0.00 sec)
mysql> show master logs;
+------------------+-----------+
| Log_name | File_size |
+------------------+-----------+
| mysql-bin.000011 | 201 |
| mysql-bin.000012 | 3068 |
| mysql-bin.000013 | 816 |
| mysql-bin.000014 | 849 |
+------------------+-----------+
4 rows in set (0.00 sec)
總結-(備份完時位置在mysql-bin.000013-(154)的地方,所以異動過後的資料紀錄於 mysql-bin.000013 & mysql-bin.000014)
下集預告: 完成以上測試資料準備後,明天開始進行後續復原數據流程